from datetime import datetime
from typing import Optional, Sequence

from pydantic import EmailStr
from sqlalchemy import and_, or_

from app.config import settings
from app.core.custom_exception import ConflictException, NotFoundException, CustomException
from app.crud.base_crud import BaseCRUD
from app.models import Device
from app.models.base import IdType
from app.models.role import Role
from app.models.user import User
from app.schemas.base_schema import LoginAccountType
from app.schemas.query_schema import BaseQueryIn
from app.schemas.result_schema import PageModel
from app.schemas.user_schema import (
    UserCreate,
    UserUpdate,
    UserUpdateAvatar,
    UserAssignPermission,
    UserUpdatePassword, UserLogin,
)

role_criteria=(Role, and_(Role.is_delete == False, Role.is_enabled == True))
device_criteria=(Device, and_(Device.is_delete == False, Device.is_enabled == True))

class UserCRUD(BaseCRUD[User, UserCreate, UserUpdate]):
    async def get_all(self,query_in: BaseQueryIn = None) -> Sequence[User]:
        """
        获取所有启用的用户
        :param query_in: 自定义查询
        :return: 用户列表
        """
        return (await self.context.dynamic_query(query_in)
                .include(relationship=self.model.roles, criteria=role_criteria)
                .include(relationship=self.model.devices, criteria=device_criteria)
                .to_list())

    async def get_page(self, current_page: int = 0, page_size: int = 100, query_in: BaseQueryIn = None,) -> PageModel[User]:
        """
        获取分页数据
        :param current_page: 跳过的记录数
        :param page_size: 每页记录数
        :param query_in: 自定义查询
        :return: 分页数据列表
        """
        return (await self.context.dynamic_query(query_in)
                    .include(relationship=self.model.roles, criteria=role_criteria)
                    .include(relationship=self.model.devices, criteria=device_criteria)
                    .to_page_list(current_page=current_page, page_size=page_size))

    async def validate_by_id(self, id: IdType) -> User:
        """
        验证数据是否存在
        :param id: 数据ID
        :return: 存在的数据对象
        :raises HTTPException: 404 如果数据不存在或已被删除
        """
        db_obj = await self.get_by_id(id=id)
        if not db_obj:
            raise NotFoundException(message=f"找不到ID为 {id} 的 {self.model.__name__}")
        return db_obj

    async def get_by_id(self, id: IdType) -> Optional[User]:
        """根据ID获取单个数据"""
        return (await self.context.where(self.model.id == id,self.model.is_enabled == True)
                .include(relationship=self.model.roles, criteria=role_criteria)
                .include(relationship=self.model.devices, criteria=device_criteria)
                .scalar_one_or_none())

    async def get_by_ids(self, ids: Sequence[IdType]) -> Sequence[User]:
        """根据ID列表获取多个数据"""
        return (await self.context.where(self.model.id.in_(ids))
                .include(relationship=self.model.roles, criteria=role_criteria)
                .include(relationship=self.model.devices, criteria=device_criteria)
                .to_list())

    async def validate_user( self,login_in: UserLogin) -> User:
        # 获取当前登录用户
        user = None
        if login_in.account_type == LoginAccountType.phone_number:
            user = await self.get_by_phone_number(phone_number=login_in.account)
        if login_in.account_type == LoginAccountType.email:
            user = await self.get_by_email(email=login_in.account)
        if login_in.account_type == LoginAccountType.username:
            user = await self.get_by_username(username=login_in.account)

        if user is None:
            raise CustomException(message="账户或者密码错误")

        # 验证密码
        if not user.verify_password(login_in.password, user.hashed_password):
            raise CustomException(message="账户或者密码错误")

        user.last_login_datetime = datetime.now()
        return await self.context.update(entity=user)

    async def get_by_username(self, username: str) -> Optional[User]:
        """
        根据用户名获取用户
        :param username: 要查询的用户名
        :return: 找到的用户对象，如果不存在则返回None
        """
        return (await self.context.where(self.model.username == username, self.model.is_enabled == True)
                .include(relationship=self.model.roles, criteria=role_criteria)
                .include(relationship=self.model.devices, criteria=device_criteria)
                .scalar_one_or_none())

    async def get_by_email(self, email: str) -> Optional[User]:
        """
        根据邮箱获取用户
        :param email: 要查询的邮箱
        :return: 找到的用户对象，如果不存在则返回None
        """
        return (await self.context.where(self.model.email == email, self.model.is_enabled == True)
                .include(relationship=self.model.roles, criteria=role_criteria)
                .include(relationship=self.model.devices, criteria=device_criteria)
                .scalar_one_or_none())

    async def get_by_phone_number(self, phone_number: str) -> Optional[User]:
        """
        根据手机号码获取用户
        :param phone_number: 手机号码
        :return: 找到的用户对象，如果不存在则返回None
        """
        return (await self.context.where(self.model.phone_number == phone_number, self.model.is_enabled == True)
                .include(relationship=self.model.roles, criteria=role_criteria)
                .include(relationship=self.model.devices, criteria=device_criteria)
                .scalar_one_or_none())

    async def reset_password(self, id: IdType) -> Optional[User]:
        """
        重置密码
        :param id: 用户ID
        :return: 重置密码成功的用户，失败则返回None
        """
        db_obj = await self.validate_by_id(id=id)
        db_obj.sqlmodel_update({"password": settings.DEFAULT_PASSWORD})
        return await self.context.update(entity=db_obj)

    async def update_password(self,password_in: UserUpdatePassword,) -> Optional[User]:
        """
        修改密码
        :param password_in: 修改密码输入
        :return: 修改密码成功的用户，失败则返回None
        """
        db_obj = await self.validate_by_id(id=password_in.id)
        if not  db_obj.verify_password(password_in.password, db_obj.hashed_password):
            return None
        db_obj.sqlmodel_update({"password": password_in.new_password})
        return await self.context.update(entity=db_obj)

    async def is_exist(self, obj: UserCreate|UserUpdate,db_obj:Optional[User]=None) ->None:
        # 检查用户名，手机号和邮箱是否已存在
        condition = (self.model.username == obj.username)
        if obj.email is not None:
            condition = (condition, self.model.email == obj.email)
        if obj.phone_number is not None:
            condition = (condition, self.model.phone_number == obj.phone_number)
        users = await self.context.where(or_(condition)).to_list()
        if users and len(users) > 0:
            conflicts = []
            for user in users:
                if user.username == obj.username:
                    if db_obj is not None and user.id == db_obj.id:
                        break
                    conflicts.append(f"用户名{obj.username}已存在")
                elif user.email == obj.email:
                    if db_obj is not None and user.id == db_obj.id:
                        break
                    conflicts.append(f"邮箱{obj.email}已存在")

                elif user.phone_number == obj.phone_number:
                    if db_obj is not None and user.id == db_obj.id:
                        break
                    conflicts.append(f"手机号{obj.phone_number}已存在")
            raise ConflictException(message=",\n".join(conflicts))


    async def create(self, obj: UserCreate) -> Optional[User]:
        """
        创建新用户
        :param obj: 用户创建数据
        :return: 创建的用户对象
        :raises ConflictException: 409 如果用户已存在
        """
        # 检查用户名，手机号和邮箱是否已存在
        await self.is_exist(obj=obj)

        db_obj = self.model.model_validate(obj)

        return await self.context.add(entity=db_obj)

    async def batch_create(self, objs: Sequence[UserCreate]) -> Sequence[User]:
        """
        批量创建用户
        :param objs: 用户创建数据列表
        :return: 创建的用户对象列表
        :raises ConflictException: 409 如果有任何用户已存在
        """
        # 检查所有用户名，手机号和邮箱是否已存在
        usernames = [obj_in.username for obj_in in objs]
        emails = [obj_in.email for obj_in in objs]
        phone_numbers = [obj_in.phone_number for obj_in in objs]

        # 查询已存在的用户名和邮箱
        existing_users = await self.context.where(self.model.username.in_(usernames)
                                                  | self.model.email.in_(emails)
                                                  | self.model.phone_number.in_(phone_numbers)).to_list()

        if existing_users:
            conflicts = []
            for existing_user in existing_users:
                if existing_user.username in usernames:
                    conflicts.append(f"用户名{existing_user.username}已存在")
                if existing_user.email in emails:
                    conflicts.append(f"邮箱{existing_user.email}已存在")
                if existing_user.phone_number in phone_numbers:
                    conflicts.append(f"手机号{existing_user.phone_number}已存在")
            raise ConflictException(message=",\n".join(conflicts))

        # 创建用户对象
        db_users = [self.model.model_validate(obj_in) for obj_in in objs]

        return await self.context.add_all(entities=db_users)

    async def update(self, obj: UserUpdate) -> Optional[User]:
        """
        更新用户
        :param obj: 更新用户模型
        :return: 更新后的用户对象
        :raises HTTPException: 409 如果用户名，手机号或者邮箱已存在 or 404 如果用户不存在
        """
        db_obj = await self.validate_by_id(id=obj.id)

        # 检查用户名，手机号和邮箱是否已存在
        await self.is_exist(obj=obj,db_obj=db_obj)

        update_data = obj.model_dump(exclude_unset=True)
        db_obj.sqlmodel_update(update_data)

        return await self.context.update(entity=db_obj)

    async def update_avatar(self, avatar_in: UserUpdateAvatar, ) -> Optional[User]:
        """
        修改用户头像
        :param avatar_in: 用户头像模型
        :return: 更新后的用户对象
        :raises HTTPException: 404 如果用户不存在
        """
        db_obj = await self.validate_by_id(id=avatar_in.id)
        return await self.context.update_field(entity=db_obj, field_name="avatar", field_value=avatar_in.avatar)

    async def assign_permissions(self,permission: UserAssignPermission) -> Optional[User]:
        """
        分配权限
        :param permission: 分配权限模型
        :return: 更新后的用户对象
        :raises HTTPException: 404 如果用户不存在
        """
        # 获取对象
        db_obj = await self.get_by_id(id=permission.id)

        if not db_obj:
            raise NotFoundException(message=f"找不到ID为 {permission.id} 的 {self.model.__name__}")

        async with self.context.nested_transaction():
            # 清空现有角色关系
            db_obj.roles.clear()

            # 如果有新角色，直接创建新的关系
            if permission.role_ids:
                # 避免使用独立查询（减少笛卡尔积风险）
                roles = await self.context.set(Role).where(and_(Role.is_enabled == True,Role.id.in_(permission.role_ids))).to_list()

                db_obj.roles.extend(roles)

        await self.context.refresh(entity=db_obj, attribute_names=["roles"])
        return db_obj

